package com.sysmanage.etl.utils;

import java.sql.Timestamp;
import java.util.Map;
import java.util.Map.Entry;

import com.sysmanage.etl.model.ETL_Property;
import com.sysmanage.etl.model.ETL_Rule;

public class SqlUtils {
	
	public static String repeatSql(ETL_Rule rule , Map<String, Object> repeatParams) {
		StringBuffer sql = new StringBuffer("select 1");
		sql.append(" from ").append(rule.getTable().getTable_name()).append(" where 1 = 1");
		for (String key : repeatParams.keySet()) {
			if (repeatParams.get(key) instanceof CharSequence || repeatParams.get(key) instanceof Timestamp) {
				sql.append(" and ").append(key).append(" = '").append(repeatParams.get(key)).append("'");
			} else {
				sql.append(" and ").append(key).append(" = ").append(repeatParams.get(key));
			}
		}
		return sql.toString();
	}
	
	public static String createSQL (ETL_Rule rule , Integer type , Map<String, Object> params , Map<String, Object> repeatParams) {
		if (type == ETL_Rule.RULE_TYPE_QUERY ) {
			return querySql ( rule , params);
		} else if (type == ETL_Rule.RULE_TYPE_UPDATE) {
			return updateSql ( rule , params , repeatParams );
		}
		return insertSql ( rule , params );
	}
	
	private static String updateSql(ETL_Rule rule, Map<String, Object> params , Map<String, Object> repeatParams) {
		StringBuffer mapping = new StringBuffer();
		StringBuffer conditions = new StringBuffer();
		for (Entry<String, Object> entry : params.entrySet()) {
			mapping.append(entry.getKey()).append(" = ");
			if(entry.getValue() instanceof CharSequence || entry.getValue() instanceof Timestamp) {
				mapping.append("'" + entry.getValue() + "' ,");
			} else {
				mapping.append(entry.getValue() + " ,");
			}
		}
		
		for (Entry<String, Object> entry : repeatParams.entrySet()) {
			conditions.append(" and ").append(entry.getKey()).append(" = ");
			if(entry.getValue() instanceof CharSequence || entry.getValue() instanceof Timestamp) {
				conditions.append("'" + entry.getValue() + "'");
			} else {
				conditions.append(entry.getValue());
			}
		}
		
		if (mapping.length() > 0) {
			mapping.deleteCharAt(mapping.length() -1 );
		}
		StringBuffer sql = new StringBuffer("update ");
		sql.append(rule.getTable().getTable_name());
		sql.append(" set ");
		sql.append(mapping);
		sql.append( " where 1 = 1");
		sql.append(conditions);
		return sql.toString();
	}
	
	private static String insertSql(ETL_Rule rule, Map<String, Object> params) {
		if (params == null || params.size() <= 0) {
			return null;
		}
		StringBuffer keys = new StringBuffer();
		StringBuffer values = new StringBuffer();
		for (Entry<String, Object> entry : params.entrySet()) {
			keys.append(entry.getKey() + ",");
			if(entry.getValue() instanceof CharSequence || entry.getValue() instanceof Timestamp) {
				values.append("'" + entry.getValue() + "',");
			} else {
				values.append(entry.getValue() + ",");
			}
		}
		if (keys.length() > 0) {
			keys.deleteCharAt(keys.length() -1 );
		}
		if (values.length() > 0) {
			values.deleteCharAt(values.length() -1 );
		}
		
		StringBuffer sql = new StringBuffer("insert into ");
		sql.append(rule.getTable().getTable_name());
		sql.append(" (" + keys);
		sql.append(") values (");
		sql.append(values + ")");
		return sql.toString();
	}
	
	
	private static String querySql(ETL_Rule rule , Map<String , Object> params) {
		ETL_Property returnProperty = rule.getRule_return_property();
		String returnProperty_Name = returnProperty == null ? "1" : returnProperty.getProperty_name();
		StringBuffer sql = new StringBuffer("select ");
		sql.append(returnProperty_Name).append(" from ").append(rule.getTable().getTable_name()).append(" where 1 = 1");
		for (String key : params.keySet()) {
			if (params.get(key) instanceof CharSequence  || params.get(key) instanceof Timestamp) {
				sql.append(" and ").append(key).append(" = '").append(params.get(key)).append("'");
			} else {
				sql.append(" and ").append(key).append(" = ").append(params.get(key));
			}
		}
		return sql.toString();
	}

}
